package net.sf.nlp.ltag.persistence;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.log4j.Logger;

import net.sf.nlp.ltag.data.ExtractedTree;
import net.sf.nlp.ltag.data.LTAGNode;
import net.sf.nlp.ltag.data.LTAGStatistics;
import net.sf.nlp.ltag.data.LTAGTree;
import net.sf.nlp.ltag.data.Word;
import net.sf.nlp.ltag.data.LTAGTree.TreeType;
import net.sf.nlp.ltag.extract.LTAGRelation;
import net.sf.nlp.ltag.util.LTAG;

public class AccessPersistence implements LTAGPersistence
{
	protected static Logger logger =  Logger.getLogger(AccessPersistence.class);
	private static String DB_FILE = "resource/parser/ltag_tables.mdb";
	private static String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=" + DB_FILE;  
	private Connection conn = null;
	
	public void createConnection() throws Exception 
	{
		 Properties prop = new Properties();    
	     prop.put("charSet", "gbk");//解决中文乱码
	     
		 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
		 conn = DriverManager.getConnection(url, prop);
	}
	
	public void close() throws Exception 
	{
		if (conn != null)
			conn.close();
	}
	
	public int execute(String sql, Object[] args) throws Exception
	{
		PreparedStatement ps = conn.prepareCall(sql);
		
		if (args != null)
		{
			for (int i = 0; i < args.length; i++)
			{
				Object arg = args[i];
				
				if (arg instanceof Object[])
					ps.setObject(i + 1, ((Object[])arg)[0], Integer.valueOf(((Object[])arg)[1].toString()));
				else
					ps.setObject(i + 1, arg);
			}
		}
		
		int ret = ps.executeUpdate();
		
		ps.close();
		
		return ret;
	}
	
	public List<Map<String, Object>> query(String sql, Object[] args) throws Exception
	{
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		
		PreparedStatement ps = conn.prepareCall(sql);
		
		if (args != null)
		{
			for (int i = 0; i < args.length; i++)
				ps.setObject(i + 1, args[i]);
		}
		
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData meteData = rs.getMetaData();
		
		int columnCount = meteData.getColumnCount();
		
		while (rs.next())
		{
			Map<String, Object> map = new HashMap<String, Object>();
			
			for (int i = 1; i <= columnCount; i++)
			{
				Object value = rs.getObject(i);
				
				if (value instanceof String)
					value = ((String)value).trim();
				map.put(meteData.getColumnLabel(i), value);
			}
			
			result.add(map);
		}
		
		rs.close();
		ps.close();
		
		return result;
	}
	
	public void createTables() throws Exception
	{
		try 
		{
			InputStream is = LTAGRelation.class.getResourceAsStream("/parser/ltag_tables.properties");
			Properties properties = new Properties();
			properties.load(is);
			is.close();
			
			Iterator<Object> tables = properties.keySet().iterator();
			
			while (tables.hasNext())
			{
				String table = (String)tables.next();
				
				try
				{
					execute("DROP TABLE " + table, null);
				}
				catch(Exception ex)
				{
					logger.error(ex, ex);
				}
				
				try
				{
					execute(properties.getProperty(table), null);
				}
				catch(Exception ex)
				{
					logger.error(ex, ex);
				}
			}
		} 
		catch (IOException e) 
		{
			e.printStackTrace();
		}
	}
	
	class AccessSave
	{
		private int treeSeq = 1;
		private int wordSeq = 1;
		private int superTreeSeq = 1;
		private int treeWordSeq = 1;
		private int treeStatSeq = 1;
		
		private static final String SQL_GET_SUPER_TREE_BY_CONTENT = "SELECT * FROM LTAG_SUPER_TREE WHERE CONTENT=?";
		private static final String SQL_GET_TREE_BY_CONTENT = "SELECT * FROM LTAG_TREE WHERE CONTENT=?";
		private static final String SQL_GET_WORD_BY_WORD = "SELECT * FROM LTAG_WORD WHERE WORD=?";
		
		private static final String SQL_ADD_SUPER_TREE = "INSERT INTO LTAG_SUPER_TREE(ID, CONTENT, POS) VALUES(?, ?, ?)";
		private static final String SQL_ADD_TREE = "INSERT INTO LTAG_TREE(ID, SUPER_TREE_ID, CONTENT) VALUES(?, ?, ?)";
		private static final String SQL_ADD_WORD = "INSERT INTO LTAG_WORD(ID, WORD) VALUES(?, ?)";
		private static final String SQL_ADD_TREE_WORD = "INSERT INTO LTAG_WORD_TREE (ID, TREE_ID, WORD_ID, AS_BEGINING, FREQUENCE) VALUES(?, ?, ?, ?, 1)";
		private static final String SQL_ADD_TREE_STATISTIC = "INSERT INTO LTAG_TREE_STATISTIC (ID, SRC_TREE_ID, DEST_TREE_ID, SRC_WORD_ID, DEST_WORD_ID, SRC_NODE_LOCATION, OPERATOR, FREQUENCE) VALUES(?, ?, ?, ?, ?, ?, ?, 1)";
		
		//private static final String SQL_UPDATE_TREE = "UPDATE LTAG_TREE SET AS_BEGINING= AS_BEGINING + 1 WHERE ID=?";
		private static final String SQL_UPDATE_TREE_WORD = "UPDATE LTAG_WORD_TREE SET FREQUENCE = FREQUENCE + 1, AS_BEGINING = AS_BEGINING + ?  WHERE TREE_ID=? AND WORD_ID=?";
		private static final String SQL_UPDATE_TREE_STATISTIC = "UPDATE LTAG_TREE_STATISTIC SET FREQUENCE= FREQUENCE + 1 WHERE SRC_TREE_ID=? AND DEST_TREE_ID=? AND SRC_WORD_ID=? AND DEST_WORD_ID=? AND SRC_NODE_LOCATION=? AND OPERATOR=?"; 
		
		private static final String SQL_IP_TREE_COUNT = "INSERT INTO LTAG_OVERALL_STATISTIC(ATTRIBUTE_NAME,ATTRIBUTE_VALUE) " +
											"SELECT 'IP_TREE_COUNT', SUM(FREQUENCE) FROM LTAG_TREE TREE, LTAG_WORD_TREE WORD_TREE WHERE MID(TREE.CONTENT,1,2)='IP' AND INSTR(TREE.CONTENT,'*')=0 AND TREE.ID=WORD_TREE.TREE_ID";
		
		private static final String SQL_ADD_TEMP_TABLE = "INSERT INTO TEMP_TABLE_STATISTIC(TREE_ID, WORD_ID, NODE_LOCATION, OPERATOR, TOTAL) " + 
																		"SELECT TREE_STAT.SRC_TREE_ID, TREE_STAT.SRC_WORD_ID, TREE_STAT.SRC_NODE_LOCATION, TREE_STAT.OPERATOR, SUM(TREE_STAT.FREQUENCE) FROM LTAG_TREE_STATISTIC TREE_STAT GROUP BY TREE_STAT.SRC_TREE_ID, TREE_STAT.SRC_WORD_ID, TREE_STAT.SRC_NODE_LOCATION, TREE_STAT.OPERATOR";
		
		private static final String SQL_ADD_TEMP_TABLE_SUPER = "INSERT INTO TEMP_TABLE_SUPER_STATISTIC(SUPER_TREE_ID, NODE_LOCATION, OPERATOR, TOTAL) " + 
																	"SELECT TREE.SUPER_TREE_ID, TREE_STAT.SRC_NODE_LOCATION, TREE_STAT.OPERATOR, SUM(TREE_STAT.FREQUENCE) FROM LTAG_TREE_STATISTIC TREE_STAT, LTAG_TREE TREE " +
																	"WHERE TREE_STAT.SRC_TREE_ID=TREE.ID GROUP BY TREE.SUPER_TREE_ID, TREE_STAT.SRC_NODE_LOCATION, TREE_STAT.OPERATOR";

		private static final String SQL_SET_TOTAL_FOR_TREE_STATISTIC = "UPDATE LTAG_TREE_STATISTIC TREE_STAT, TEMP_TABLE_STATISTIC TEMP_STAT SET TREE_STAT.TOTAL=TEMP_STAT.TOTAL WHERE TREE_STAT.SRC_TREE_ID=TEMP_STAT.TREE_ID AND TREE_STAT.SRC_WORD_ID=TEMP_STAT.WORD_ID AND TREE_STAT.SRC_NODE_LOCATION=TEMP_STAT.NODE_LOCATION AND TREE_STAT.OPERATOR=TEMP_STAT.OPERATOR";
		
		private static final String SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC0 = "INSERT INTO LTAG_SUPER_TREE_STATISTIC(SRC_SUPER_TREE_ID, SRC_NODE_LOCATION, DEST_SUPER_TREE_ID, OPERATOR, FREQUENCE, TOTAL)" +
																				"SELECT TREE.SUPER_TREE_ID, -1, NULL, NULL, SUM(WORD_TREE.FREQUENCE) AS FREQUENCE, -1 " +
																				"FROM LTAG_WORD_TREE AS WORD_TREE, LTAG_TREE AS TREE " +
																				"WHERE WORD_TREE.TREE_ID=TREE.ID " +
																				"GROUP BY TREE.SUPER_TREE_ID";

		private static final String SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC1 = "INSERT INTO LTAG_SUPER_TREE_STATISTIC(SRC_SUPER_TREE_ID, SRC_NODE_LOCATION, DEST_SUPER_TREE_ID, OPERATOR, FREQUENCE, TOTAL)" +
																				"SELECT TREE1.SUPER_TREE_ID, TREE_STAT.SRC_NODE_LOCATION, TREE2.SUPER_TREE_ID, TREE_STAT.OPERATOR, SUM(TREE_STAT.FREQUENCE) AS FREQUENCE, SUM(TREE_STAT.TOTAL) AS TOTAL " +
																				"FROM (LTAG_TREE_STATISTIC AS TREE_STAT INNER JOIN LTAG_TREE AS TREE1 ON TREE_STAT.SRC_TREE_ID=TREE1.ID) " +
																				"     LEFT JOIN LTAG_TREE AS TREE2 ON TREE_STAT.DEST_TREE_ID=TREE2.ID " +
																				"GROUP BY TREE1.SUPER_TREE_ID, TREE_STAT.SRC_NODE_LOCATION, TREE2.SUPER_TREE_ID, TREE_STAT.OPERATOR";
		
		private static final String SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC2 = "UPDATE LTAG_SUPER_TREE_STATISTIC STAT, TEMP_TABLE_SUPER_STATISTIC TEMP " +
																				"SET STAT.TOTAL=TEMP.TOTAL " + 
																				"WHERE STAT.SRC_SUPER_TREE_ID=TEMP.SUPER_TREE_ID AND STAT.SRC_NODE_LOCATION=TEMP.NODE_LOCATION AND STAT.OPERATOR=TEMP.OPERATOR ";
		
		public void saveTrees(List<ExtractedTree> etrees, boolean isCreateTables) throws Exception
		{
			createConnection();
			
			if (isCreateTables)
				createTables();
			
			for (ExtractedTree etree : etrees)
			{
				int superTreeId = saveOrUpdateSuperTree(etree);
				int etreeId = saveOrUpdateTree(etree, superTreeId);
				int wordId = saveOrUpdateWord(etree);
				
				saveOrUpdateWordTree(etree, etreeId, wordId);
				saveOrUpdateTreeStatistic(etree, etreeId, wordId);
			}
			
			statistics();
			
			close();
		}
		
		private int saveOrUpdateSuperTree(ExtractedTree etree) throws Exception
		{
			int superTreeId;
			List<Map<String, Object>> result = query(SQL_GET_SUPER_TREE_BY_CONTENT, new Object[]{etree.toSuperLTAG()});
			
			if (result.size() == 0)
			{
				superTreeId = superTreeSeq++;
				
				execute(SQL_ADD_SUPER_TREE, new Object[]{superTreeId, etree.toSuperLTAG(), etree.getAnchor().getNodeText()});
			}
			else
			{
				superTreeId = Integer.valueOf(result.get(0).get("ID").toString());
			}
			
			return superTreeId;
		}
		
		private int saveOrUpdateTree(ExtractedTree etree, int superTreeId) throws Exception
		{
			int etreeId;
			List<Map<String, Object>> result = query(SQL_GET_TREE_BY_CONTENT, new Object[]{etree.toLTAGNoWord()});
			
			if (result.size() == 0)
			{
				etreeId = treeSeq++;
				
				execute(SQL_ADD_TREE, new Object[]{etreeId, superTreeId, etree.toLTAGNoWord()});
			}
			else
			{
				etreeId = Integer.valueOf(result.get(0).get("ID").toString());
			}
			
			return etreeId;
		}
		
		private int saveOrUpdateWord(ExtractedTree etree) throws Exception
		{
			int wordId;
			
			LTAGNode anchord = etree.getAnchor();
			List<Map<String, Object>> result = query(SQL_GET_WORD_BY_WORD, new Object[]{anchord.getWord().getLexical()});
			
			if (result.size() == 0)
			{
				wordId = wordSeq++;
				execute(SQL_ADD_WORD, new Object[]{wordId, anchord.getWord().getLexical()});
			}
			else
			{
				wordId = Integer.valueOf(result.get(0).get("ID").toString());
			}
			
			return wordId;
		}
		
		private void saveOrUpdateWordTree(ExtractedTree etree, int etreeId, int wordId) throws Exception
		{
			int addValue = 0;
			if (etree.isBeginning())
				addValue = 1;
			
			if (execute(SQL_UPDATE_TREE_WORD, new Object[]{addValue, etreeId, wordId}) == 0)
				execute(SQL_ADD_TREE_WORD, new Object[]{treeWordSeq++, etreeId, wordId, addValue});
		}
		
		private void saveOrUpdateTreeStatistic(ExtractedTree etree, int srcEtreeId, int srcWordId) throws Exception
		{
			Map<LTAGNode, List<ExtractedTree>> operatorMap = etree.getOperatorMap();
			
			Iterator<LTAGNode> nodes = operatorMap.keySet().iterator();
			while (nodes.hasNext())
			{
				LTAGNode node = nodes.next();
				List<ExtractedTree> list = operatorMap.get(node);
				
				String operator = "$";
				
				if (list.size() == 0)
				{
					operator = "*";
					if (execute(SQL_UPDATE_TREE_STATISTIC, new Object[]{srcEtreeId, new Object[]{null, Types.INTEGER}, srcWordId, new Object[]{null, Types.INTEGER}, node.getLocation(), operator}) == 0)
						execute(SQL_ADD_TREE_STATISTIC, new Object[]{treeStatSeq++, srcEtreeId, new Object[]{null, Types.INTEGER}, srcWordId, new Object[]{null, Types.INTEGER}, node.getLocation(), operator});
				}
				else
				{
					for (ExtractedTree et : list)
					{
						if (et.getType() == TreeType.A)
							operator = "*";
						
						int superTreeId = saveOrUpdateSuperTree(et);
						int destEtreeId = saveOrUpdateTree(et, superTreeId);
						int destWordId = saveOrUpdateWord(et);
						
						if (execute(SQL_UPDATE_TREE_STATISTIC, new Object[]{srcEtreeId, destEtreeId, srcWordId, destWordId, node.getLocation(), operator}) == 0)
							execute(SQL_ADD_TREE_STATISTIC, new Object[]{treeStatSeq++, srcEtreeId, destEtreeId, srcWordId, destWordId, node.getLocation(), operator});
					}
				}
			}
		}
		
		private void statistics() throws Exception
		{
			execute(SQL_IP_TREE_COUNT, null);
			execute(SQL_ADD_TEMP_TABLE, null);
			execute(SQL_ADD_TEMP_TABLE_SUPER, null);
			execute(SQL_SET_TOTAL_FOR_TREE_STATISTIC, null);
			execute(SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC0, null);
			execute(SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC1, null);
			execute(SQL_SET_TOTAL_FOR_SUPER_TREE_STATISTIC2, null);
		}
	}
	
	class AccessLoad
	{
		private static final String SQL_GET_SUPER_TREES = "SELECT * FROM LTAG_SUPER_TREE";
		private static final String SQL_GET_LTAG_TREES = "SELECT T.ID, W.WORD, T.CONTENT FROM LTAG_TREE T, LTAG_WORD W, LTAG_WORD_TREE WT WHERE T.ID=WT.TREE_ID AND W.ID=WT.WORD_ID";
		private static final String SQL_GET_LTAG_SUPER_TREES = "SELECT DISTINCT S.ID, W.WORD, S.CONTENT FROM LTAG_TREE T, LTAG_SUPER_TREE S, LTAG_WORD W, LTAG_WORD_TREE WT WHERE T.SUPER_TREE_ID=S.ID AND T.ID=WT.TREE_ID AND W.ID=WT.WORD_ID";
		
		private static final String SQL_GET_SUPER_TREE_STATISTIC = "SELECT * FROM LTAG_SUPER_TREE_STATISTIC";
		private static final String SQL_GET_OVERALL_STATISTIC = "SELECT * FROM LTAG_OVERALL_STATISTIC";
		
		public Map<String, List<LTAGTree>> loadTrees()
		{
			Map<String, List<LTAGTree>>  ltagMap = new HashMap<String, List<LTAGTree>>();
			
			try 
			{
				createConnection();
				
				ltagMap.putAll(loadSuperTree());
				ltagMap.putAll(loadLTAGTree());
				
				loadStatistic();
				
				close();
			}
			catch (Exception e) 
			{
				logger.error(e.toString(), e);
			} 
			
			return ltagMap;
		}
		
		private void loadStatistic() throws Exception
		{
			Map<String, Long> overallStatMap = loadOverallStatistic();
			
			LTAGStatistics.setBeginningTreeCount(overallStatMap.get("IP_TREE_COUNT"));
			
			List<Map<String, Object>> result = query(SQL_GET_SUPER_TREE_STATISTIC, null);
			
			for (Map<String, Object> row : result)
			{
				Long srcSuperTreeId = Long.valueOf(row.get("SRC_SUPER_TREE_ID").toString());
				Integer srcNodeLocation = Integer.valueOf(row.get("SRC_NODE_LOCATION").toString());
				Long  frequence = Long.valueOf(row.get("FREQUENCE").toString());
				Long total = Long.valueOf(row.get("TOTAL").toString());
				
				Long destSuperTreeId = null;
				String operator = null;
				
				if (row.get("DEST_SUPER_TREE_ID") != null)
					destSuperTreeId = Long.valueOf(row.get("DEST_SUPER_TREE_ID").toString());
				if (row.get("OPERATOR") != null)
					operator = row.get("OPERATOR").toString();
				
				LTAGStatistics.addStatistic(srcSuperTreeId, destSuperTreeId, srcNodeLocation, operator, frequence, total);
			}
		}
		
		private Map<String, Long> loadOverallStatistic() throws Exception
		{
			Map<String, Long> overallStatMap = new HashMap<String, Long>();
			
			List<Map<String, Object>> result = query(SQL_GET_OVERALL_STATISTIC, null);
			
			for (Map<String, Object> row : result)
				overallStatMap.put(row.get("ATTRIBUTE_NAME").toString(), Long.valueOf(row.get("ATTRIBUTE_VALUE").toString()));
			
			return overallStatMap;
		}
		
		private Map<String, List<LTAGTree>> loadSuperTree() throws Exception
		{
			Map<String, List<LTAGTree>>  ltagMap = new HashMap<String, List<LTAGTree>>();
			
			List<Map<String, Object>> result = query(SQL_GET_SUPER_TREES, null);
			
			for (Map<String, Object> row : result)
			{
				LTAGTree ltagTree = new LTAGTree();
				
				ltagTree.setId(Long.valueOf(row.get("ID").toString()));
				ltagTree.setRoot(LTAG.buildLTAGNodeFromText(row.get("CONTENT").toString()));
				ltagTree.validate();
				
				buildLTAGMap(ltagTree, ltagMap);
			}
			
			return ltagMap;
		}
		
		private Map<String, List<LTAGTree>> loadLTAGTree() throws Exception
		{
			Map<String, List<LTAGTree>>  ltagMap = new HashMap<String, List<LTAGTree>>();
			
			String sql = SQL_GET_LTAG_TREES;
			
			if (Boolean.valueOf(LTAG.ltagProperties.getProperty("ONLY_USE_SUPER_TAG")))
				sql = SQL_GET_LTAG_SUPER_TREES;
			
			List<Map<String, Object>> result = query(sql, null);
			
			for (Map<String, Object> row : result)
			{
				Word word = new Word();
				LTAGTree ltagTree = new LTAGTree();
				
				word.setLexical(row.get("WORD").toString());
				
				ltagTree.setId(Long.valueOf(row.get("ID").toString()));
				ltagTree.setRoot(LTAG.buildLTAGNodeFromText(row.get("CONTENT").toString()));
				ltagTree.appendWord(word);
				ltagTree.validate();
				
				buildLTAGMap(ltagTree, ltagMap);
			}
			
			return ltagMap;
		}
		
		private void buildLTAGMap(LTAGTree ltagTree, Map<String, List<LTAGTree>>  ltagMap)
		{
			LTAGNode anchor = ltagTree.getAnchor();
			
			if (anchor == null)
				logger.error("The LTAG TREE HAS SOMETHING WRONG." + ltagTree);
			
			Word word = anchor.getWord();
			
			if (word != null)
			{
				List<LTAGTree> list = ltagMap.get(word.getLexical());
				if (list == null)
				{
					list = new ArrayList<LTAGTree>();
					ltagMap.put(word.getLexical(), list);
				}
				
				list.add(ltagTree);
				
				return;
			}
			
			List<LTAGTree> list = ltagMap.get(anchor.getNodeText());
			if (list == null)
			{
				list = new ArrayList<LTAGTree>();
				ltagMap.put(anchor.getNodeText(), list);
			}
			
			list.add(ltagTree);
		}
	}
	
	@Override
	public Map<String, List<LTAGTree>> loadTrees() {
		return new AccessLoad().loadTrees();
	}
	
	@Override
	public void saveTrees(List<ExtractedTree> etrees) throws Exception {
		new AccessSave().saveTrees(etrees, true);
	}
	
	public static void main(String[] args) throws Exception
	{
		AccessPersistence access = new AccessPersistence();
		
		access.createConnection();
		
		access.createTables();
		access.close();
	}



}
